package impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import dao.SubTaskDao;
import model.Subtask;


import util.C3P0Util;
/**
 * 
 * @author simon
 *
 */



public class SubtaskDaoImpl implements SubTaskDao {

	@Override
	public List<Subtask> queryByItemId(Integer itemId) throws SQLException {
		Subtask subtask = null;
		List<Subtask> list = new ArrayList<Subtask>();
		Connection conn = null;
		try {
			conn = C3P0Util.getConnection();
			String sql = " select * from `t_subtask`"
						+ " where item_id = ? ";			
			PreparedStatement ps = conn.prepareStatement(sql);			
			ps.setInt(1, itemId);		
			
			ResultSet rs = ps.executeQuery();
			
			if( rs.next() ){
				subtask = new Subtask();
				subtask.setId( rs.getInt("id") );
				subtask.setItem_id( rs.getInt("item_id") );
				subtask.setName(rs.getString("name"));
				subtask.setTask_detail(rs.getString("task_detail"));
				list.add(subtask);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}finally{
			C3P0Util.close(conn);
		}
		return list;
	}

	@Override
	public Subtask queryBySubtaskId(Integer subId) throws SQLException {
		Subtask subtask = null;
		Connection conn = null;
		try {
			conn = C3P0Util.getConnection();
			String sql = " select * from `t_subtask`"
						+ " where id = ? ";			
			PreparedStatement ps = conn.prepareStatement(sql);			
			ps.setInt(1, subId);		
			
			ResultSet rs = ps.executeQuery();
			
			if( rs.next() ){
				subtask = new Subtask();
				subtask.setId( rs.getInt("id") );
				subtask.setItem_id( rs.getInt("item_id") );
				subtask.setName(rs.getString("name"));
				subtask.setTask_detail(rs.getString("task_detail"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}finally{
			C3P0Util.close(conn);
		}
		return subtask;
	}

	@Override
	public void addSubtask(Integer itemId, String name, String task_detail) throws SQLException {

		Connection conn = null;
		try {
			conn = C3P0Util.getConnection();
			String sql = "insert into `t_subtask`(item_id, name, task_detail)"
						+" values(?,?,?)	";	
			PreparedStatement ps = conn.prepareStatement(sql);			
			ps.setInt(1, itemId);	
			ps.setString(2, name);
			ps.setString(3, task_detail);
			
			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			throw e;			
		}finally{
			C3P0Util.close(conn);
		}
	}

	@Override
	public void deleteSubtaskById(Integer subId) throws SQLException {
		Connection conn = null;
		try {
			conn = C3P0Util.getConnection();
			String sql ="delete from `t_subtask`  where id =?";	
			PreparedStatement ps = conn.prepareStatement(sql);			
			ps.setInt(1, subId);	
			
			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			throw e;			
		}finally{
			C3P0Util.close(conn);
		}
	}

}
